#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
地图热点数据库管理模块
"""

import sqlite3
import json
import os
from datetime import datetime
from typing import Dict, List, Optional, Tuple

class HotspotDatabase:
    def __init__(self, db_path: str = 'map_hotspots.db'):
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库表结构"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建图片表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS images (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    src TEXT NOT NULL,
                    width INTEGER,
                    height INTEGER,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建热点表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS hotspots (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    image_id INTEGER NOT NULL,
                    href TEXT NOT NULL UNIQUE,
                    coords TEXT NOT NULL,
                    hotspot_id INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (image_id) REFERENCES images (id) ON DELETE CASCADE
                )
            ''')
            
            # 创建热点信息表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS hotspot_info (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    href TEXT NOT NULL UNIQUE,
                    content TEXT NOT NULL,
                    name TEXT DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 检查并添加name字段（如果不存在）
            cursor.execute("PRAGMA table_info(hotspot_info)")
            columns = [col[1] for col in cursor.fetchall()]
            if 'name' not in columns:
                cursor.execute('ALTER TABLE hotspot_info ADD COLUMN name TEXT DEFAULT NULL')
                print("✅ hotspot_info表已添加name字段")
            
            # 创建配置表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS config (
                    key TEXT PRIMARY KEY,
                    value TEXT NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建索引
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_hotspots_image_id ON hotspots(image_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_hotspots_href ON hotspots(href)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_hotspot_info_href ON hotspot_info(href)')
            
            conn.commit()
            print("✅ 数据库表结构初始化完成")
    
    def add_image(self, src: str, width: Optional[int] = None, height: Optional[int] = None) -> int:
        """添加图片记录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO images (src, width, height, updated_at)
                VALUES (?, ?, ?, ?)
            ''', (src, width, height, datetime.now().isoformat()))
            return cursor.lastrowid
    
    def add_hotspot(self, image_id: int, href: str, coords: str, hotspot_id: int) -> int:
        """添加热点记录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT OR REPLACE INTO hotspots (image_id, href, coords, hotspot_id, updated_at)
                VALUES (?, ?, ?, ?, ?)
            ''', (image_id, href, coords, hotspot_id, datetime.now().isoformat()))
            return cursor.lastrowid
    
    def add_hotspot_info(self, href: str, content: str) -> int:
        """添加热点信息"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT OR REPLACE INTO hotspot_info (href, content, updated_at)
                VALUES (?, ?, ?)
            ''', (href, content, datetime.now().isoformat()))
            return cursor.lastrowid
    
    def get_all_images(self) -> List[Dict]:
        """获取所有图片及其热点"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            # 获取所有图片
            cursor.execute('SELECT * FROM images ORDER BY id')
            images = []
            
            for image_row in cursor.fetchall():
                image = dict(image_row)
                
                # 获取该图片的所有热点
                hotspot_cursor = conn.cursor()
                hotspot_cursor.execute('''
                    SELECT href, coords, hotspot_id 
                    FROM hotspots 
                    WHERE image_id = ? 
                    ORDER BY hotspot_id
                ''', (image['id'],))
                
                hotspots = []
                for hotspot_row in hotspot_cursor.fetchall():
                    hotspots.append({
                        'id': hotspot_row['hotspot_id'],
                        'coords': hotspot_row['coords'],
                        'href': hotspot_row['href']
                    })
                
                image['hotspots'] = hotspots
                images.append(image)
            
            return images
    
    def get_all_hotspot_info(self) -> Dict[str, str]:
        """获取所有热点信息"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute('SELECT href, content FROM hotspot_info')
            
            return {row['href']: row['content'] for row in cursor.fetchall()}
    
    def get_all_hotspot_names(self) -> Dict[str, str]:
        """获取所有热点自定义名称"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute('SELECT href, name FROM hotspot_info WHERE name IS NOT NULL AND name != ""')
            
            return {row['href']: row['name'] for row in cursor.fetchall()}
    
    def update_hotspot_info(self, href: str, content: str) -> bool:
        """更新热点信息，如果不存在则创建"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 先尝试更新
                cursor.execute('''
                    UPDATE hotspot_info 
                    SET content = ?, updated_at = ? 
                    WHERE href = ?
                ''', (content, datetime.now().isoformat(), href))
                
                # 如果没有更新任何行，说明记录不存在，需要插入
                if cursor.rowcount == 0:
                    cursor.execute('''
                        INSERT INTO hotspot_info (href, content, created_at, updated_at)
                        VALUES (?, ?, ?, ?)
                    ''', (href, content, datetime.now().isoformat(), datetime.now().isoformat()))
                
                return True
        except Exception as e:
            print(f"更新热点信息失败: {e}")
            return False
    
    def update_hotspot_name(self, hotspot_id: str, name: str) -> bool:
        """更新热点名称，如果热点信息不存在则创建"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 先检查是否存在该热点的记录
                cursor.execute('SELECT id FROM hotspot_info WHERE href = ?', (hotspot_id,))
                exists = cursor.fetchone()
                
                if exists:
                    # 更新名称
                    cursor.execute('''
                        UPDATE hotspot_info 
                        SET name = ?, updated_at = ? 
                        WHERE href = ?
                    ''', (name, datetime.now().isoformat(), hotspot_id))
                else:
                    # 创建新记录
                    cursor.execute('''
                        INSERT INTO hotspot_info (href, content, name, created_at, updated_at)
                        VALUES (?, ?, ?, ?, ?)
                    ''', (hotspot_id, '', name, datetime.now().isoformat(), datetime.now().isoformat()))
                
                return True
        except Exception as e:
            print(f"更新热点名称失败: {e}")
            return False
    
    def update_hotspot_coords(self, href: str, coords: str) -> bool:
        """更新热点坐标"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE hotspots 
                SET coords = ?, updated_at = ? 
                WHERE href = ?
            ''', (coords, datetime.now().isoformat(), href))
            return cursor.rowcount > 0
    
    def delete_image(self, image_id: int) -> bool:
        """删除图片及其所有热点"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 获取要删除的热点hrefs
            cursor.execute('SELECT href FROM hotspots WHERE image_id = ?', (image_id,))
            hrefs = [row[0] for row in cursor.fetchall()]
            
            # 删除热点信息
            for href in hrefs:
                cursor.execute('DELETE FROM hotspot_info WHERE href = ?', (href,))
            
            # 删除热点
            cursor.execute('DELETE FROM hotspots WHERE image_id = ?', (image_id,))
            
            # 删除图片
            cursor.execute('DELETE FROM images WHERE id = ?', (image_id,))
            
            return cursor.rowcount > 0
    
    def delete_hotspot(self, href: str) -> bool:
        """删除单个热点"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 删除热点信息
            cursor.execute('DELETE FROM hotspot_info WHERE href = ?', (href,))
            
            # 删除热点
            cursor.execute('DELETE FROM hotspots WHERE href = ?', (href,))
            
            return cursor.rowcount > 0
    
    def get_next_hotspot_id(self) -> int:
        """获取下一个热点ID"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('SELECT COALESCE(MAX(hotspot_id), 0) + 1 FROM hotspots')
            return cursor.fetchone()[0]
    
    def set_config(self, key: str, value: str):
        """设置配置项"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT OR REPLACE INTO config (key, value, updated_at)
                VALUES (?, ?, ?)
            ''', (key, value, datetime.now().isoformat()))
    
    def get_config(self, key: str, default: str = None) -> Optional[str]:
        """获取配置项"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('SELECT value FROM config WHERE key = ?', (key,))
            row = cursor.fetchone()
            return row[0] if row else default
    
    def export_to_json_format(self) -> Dict:
        """导出数据为JSON格式（兼容原有格式）"""
        images = self.get_all_images()
        hotspot_info = self.get_all_hotspot_info()
        hotspot_names = self.get_all_hotspot_names()
        next_hotspot_id = self.get_next_hotspot_id()
        
        return {
            'imageData': {
                'images': images
            },
            'hotspotInfo': hotspot_info,
            'hotspotNames': hotspot_names,
            'nextHotspotId': next_hotspot_id,
            'lastUpdated': datetime.now().isoformat()
        }


def migrate_from_json(json_file: str = 'map_hotspots_data.json', db_path: str = 'map_hotspots.db'):
    """从JSON文件迁移数据到SQLite数据库"""
    if not os.path.exists(json_file):
        print(f"❌ JSON文件 {json_file} 不存在")
        return False
    
    # 创建数据库备份
    if os.path.exists(db_path):
        backup_path = f"{db_path}.backup.{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        os.rename(db_path, backup_path)
        print(f"📦 已备份现有数据库到: {backup_path}")
    
    # 初始化数据库
    db = HotspotDatabase(db_path)
    
    try:
        # 读取JSON数据
        with open(json_file, 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        print("📊 开始迁移数据...")
        
        # 迁移图片数据
        images = data.get('imageData', {}).get('images', [])
        image_id_mapping = {}  # 原始ID到新ID的映射
        
        for image in images:
            original_id = image['id']
            new_id = db.add_image(
                src=image['src'],
                width=image.get('width'),
                height=image.get('height')
            )
            image_id_mapping[original_id] = new_id
            
            # 迁移该图片的热点
            for hotspot in image.get('hotspots', []):
                db.add_hotspot(
                    image_id=new_id,
                    href=hotspot['href'],
                    coords=hotspot['coords'],
                    hotspot_id=hotspot['id']
                )
        
        # 迁移热点信息
        hotspot_info = data.get('hotspotInfo', {})
        for href, content in hotspot_info.items():
            db.add_hotspot_info(href, content)
        
        # 设置下一个热点ID
        next_hotspot_id = data.get('nextHotspotId', 1)
        db.set_config('next_hotspot_id', str(next_hotspot_id))
        
        print(f"✅ 数据迁移完成！")
        print(f"   - 图片数量: {len(images)}")
        print(f"   - 热点数量: {sum(len(img.get('hotspots', [])) for img in images)}")
        print(f"   - 热点信息: {len(hotspot_info)}")
        
        # 验证数据
        verify_migration(db, data)
        
        return True
        
    except Exception as e:
        print(f"❌ 数据迁移失败: {e}")
        return False


def verify_migration(db: HotspotDatabase, original_data: Dict):
    """验证迁移的数据完整性"""
    print("\n🔍 验证数据完整性...")
    
    exported_data = db.export_to_json_format()
    
    # 验证图片数量
    original_images = original_data.get('imageData', {}).get('images', [])
    exported_images = exported_data.get('imageData', {}).get('images', [])
    
    if len(original_images) != len(exported_images):
        print(f"⚠️ 图片数量不匹配: 原始 {len(original_images)}, 导出 {len(exported_images)}")
        return False
    
    # 验证热点数量
    original_hotspots = sum(len(img.get('hotspots', [])) for img in original_images)
    exported_hotspots = sum(len(img.get('hotspots', [])) for img in exported_images)
    
    if original_hotspots != exported_hotspots:
        print(f"⚠️ 热点数量不匹配: 原始 {original_hotspots}, 导出 {exported_hotspots}")
        return False
    
    # 验证热点信息数量
    original_info = original_data.get('hotspotInfo', {})
    exported_info = exported_data.get('hotspotInfo', {})
    
    if len(original_info) != len(exported_info):
        print(f"⚠️ 热点信息数量不匹配: 原始 {len(original_info)}, 导出 {len(exported_info)}")
        return False
    
    print("✅ 数据完整性验证通过！")
    return True


if __name__ == '__main__':
    # 执行数据迁移
    success = migrate_from_json()
    if success:
        print("\n🎉 数据迁移成功完成！")
        print("💡 建议：")
        print("   1. 测试新的数据库功能")
        print("   2. 确认所有热点位置正确")
        print("   3. 备份原始JSON文件")
    else:
        print("\n❌ 数据迁移失败，请检查错误信息")


